/*++

Copyright (c) 2007  Microsoft Corporation 

Filename: 

    ExcelServiceManager.cs

Abstract: 

    This file consists of all the Office Excel processing methods
     
--*/
#region References
using System;
using System.Collections.Generic;
using System.Text;
using UtilityManager;
using Microsoft.Win32;
using System.Data.OleDb;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Globalization;
using System.Reflection;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Diagnostics;
#endregion References

namespace ExcelManager
{
    /// <summary>
    /// Class defines methods to process Excel File
    /// </summary>
    public class ExcelServiceManager
    {
        #region Private Variables
        private static OleDbConnection  excelConnection;
        private static OleDbDataAdapter excelAdapter;
        private static Excel.Application ExcelReportFile;
        private static Excel.Workbook WorkBook;
        private static Excel.Sheets WorkSheets;
        private static Excel.Worksheet WorkSheetColo;
        private static Excel.Worksheet WorkSheetRack;
        private static Excel.Worksheet WorkSheetServer;
        private static SaveFileDialog saveFileDialog;
        #endregion Private Variables

        #region Methods
        /// <summary>
        /// Check if MS Excel installed
        /// </summary>
        /// <returns>True if excel is installed</returns>
        public static bool ExcelInstalled()
        {
            // Registry Key Paths
            string officeExcelKey2007 = @"SOFTWARE\Microsoft\Office\12.0\Excel";
            string officeExcelKey2003 = @"SOFTWARE\Microsoft\Office\11.0\Excel";

            // Check if excel installed using registry
            RegistryKey regKeyExcel2003 = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine,
                System.Environment.MachineName).OpenSubKey(officeExcelKey2003);
            RegistryKey regKeyExcel2007 = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine,
                System.Environment.MachineName).OpenSubKey(officeExcelKey2007);

            if (regKeyExcel2003 == null && regKeyExcel2007 == null)
            {
                // Excel not installed 
                return false;
            }

            // Excel installed
            return true;
        }

        /// <summary>
        /// To open an Excel file template
        /// </summary>
        /// <param name="fileName"></param>
        public static void OpenExcelFile(string fileName)
        {
            Process runner = new Process();

            // Get the Excel installed path through registry
            RegistryKey regKey = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Office\12.0\Excel\InstallRoot");
            if(regKey != null)
            {                
                runner.StartInfo.FileName = regKey.GetValue("Path").ToString() + "EXCEL.EXE";
            }

            // Pass the arguments to Excel process
            runner.StartInfo.Arguments = "\"" + fileName + "\"";
            runner.Start();
            bool ok = runner.HasExited;
            while (!ok)
            {
                ok = runner.HasExited;
            }
            return;
        }

        /// <summary>
        /// This mehtod retrieves the excel sheet names from an excel workbook.
        /// </summary>
        /// <param name="excelFile">The excel file.</param>
        /// <returns>String array consists of excel sheet names</returns>
        public static string[] GetExcelSheetNames(string excelFile)
        {           
            // Temporary datatable
            DataTable dtExcelSheets = null;

            try
            {  
                // Connection String to connect to the desired excel file.
                string excelConnString =                        
                            @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                            @"Data Source="+excelFile+";" +
                            @"Mode=Read;" +
                            @"Extended Properties=""HDR=YES;IMEX=1;MaxScanRows=0;Excel 12.0;"";" +
                            @"Jet OLEDB:Engine Type=35;";

                // Make excel connection string global
                Global.ExcelConnString = excelConnString;

                // Create connection object by using the preceding connection string.
                excelConnection = new OleDbConnection(excelConnString);
                // Open connection with the database.
                excelConnection.Open();
                // Get the data table containg the schema guid.
                dtExcelSheets = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                if (dtExcelSheets == null)
                {
                    return null;
                }

                // String array to store excel sheet names
                string[] excelSheets = new string[dtExcelSheets.Rows.Count];
              
                // Sheet index counter
                int sheetCtr = 0;              

                // Add the sheet name to the string array.
                foreach (DataRow row in dtExcelSheets.Rows)
                {
                    excelSheets[sheetCtr] = row["TABLE_NAME"].ToString();
                    sheetCtr++;
                }              

                // Excel sheet names
                return excelSheets;
            }
            catch (OleDbException ex)
            {
                // If exception occurs during connection open
                MessageBox.Show(ex.Message + Global.GetResourceString("OleDBException_Text"),
                        Global.GetResourceString("MessageBoxCaption"),
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Error,
                        MessageBoxDefaultButton.Button1
                        );
                return null;
            }
            catch (RackException ex)
            {
                // Exception from sub function. Pass on to entry point
                throw new RackException("Get Excel Sheets Name", ex.InnerException);                
               
            }
            catch (Exception ex)
            {
                // Exception from sub function. Pass on to entry point
                throw new RackException("Get Excel Sheets Name", ex.InnerException);                
               
            }           
            finally
            {
                // Clean up.
                if (excelConnection != null)
                {
                    excelConnection.Close();
                    excelConnection.Dispose();
                }
                if (dtExcelSheets != null)
                {
                    dtExcelSheets.Dispose();
                }
            }
        }

        /// <summary>
        /// Retrieves the column names from different excel sheets
        /// </summary>
        /// <param name="sqlSelect">Sql Query</param>
        /// <returns>String array consists of Excel column names</returns>
        public static string[] GetColumnNamesFromExcel(string sqlSelect)
        {
            try
            {
               // Temp datatable
               DataTable dtSheetColumns = new DataTable();
               dtSheetColumns.Locale = CultureInfo.InvariantCulture;
                
               // Connection String to connect to the desired excel file.
               string excelConnectionString = Global.ExcelConnString;

               // DataAdapter that contains data from excel file 
               excelAdapter = new OleDbDataAdapter(sqlSelect, excelConnectionString);                                     

               // DataAdapter fills datatable with data
               excelAdapter.Fill(dtSheetColumns);

               // String array to store sheet column names
               string[] sheetColumns = new string[dtSheetColumns.Columns.Count];

               // Add the sheet column names to the string array. 
               for (int colIndex = 0, sheetIndex = 0; (colIndex < dtSheetColumns.Columns.Count)
                   & (sheetIndex < dtSheetColumns.Columns.Count); colIndex++, sheetIndex++)
                {
                    sheetColumns[sheetIndex] = dtSheetColumns.Columns[colIndex].ColumnName;
                }               
                
                // Excel sheet column names
                return sheetColumns;
            }
            catch (RackException ex)
            {
                // Exception from sub function. Pass on to entry point
                throw new RackException("Get Excel Columns Name", ex.InnerException);
                
            }
            catch (Exception ex)
            {
                // Exception from sub function. Pass on to entry point
                throw new RackException("Get Excel Columns Name", ex.InnerException);
               
            }     
            finally
            {
                // Object Clean up.
                if (excelAdapter != null)
                {
                    excelAdapter.Dispose();
                }
            }
        }

        /// <summary>
        /// Export DataGridView Results To MSExcel
        /// </summary>
        /// <param name="DataGridViewReportColo">Colo grid data</param>
        /// <param name="DataGridViewReportRack">Rack grid data</param>
        /// <param name="DataGridViewReportServer">Server grid data</param>
        public static void ExportToExcel(DataGridView dataGridViewColo, 
            DataGridView dataGridViewRack, DataGridView dataGridViewServer)
        {           
            // Get datagridview rows into DataGridViewRowCollection
            DataGridViewRowCollection dgvrcColo=null;
            DataGridViewRowCollection dgvrcRack=null;
            DataGridViewRowCollection dgvrcServer=null ;

            if (dataGridViewColo != null && dataGridViewRack != null && dataGridViewServer != null)
            {
                dgvrcColo = dataGridViewColo.Rows;
                dgvrcRack = dataGridViewRack.Rows;
                dgvrcServer = dataGridViewServer.Rows;
                try
                {
                    // Save file dialog
                    saveFileDialog = new System.Windows.Forms.SaveFileDialog();

                    // If excel not installed
                    if (!ExcelInstalled())
                    {
                        MessageBox.Show(Global.GetResourceString("Excel_Install"),
                            Global.GetResourceString("MessageBoxCaption"),
                            MessageBoxButtons.OK, MessageBoxIcon.Error,
                            MessageBoxDefaultButton.Button1
                            );
                        return;
                    }

                    // Format datetime to add in report name
                    DateTime dateTime = DateTime.Now;
                    CultureInfo culture = new CultureInfo("ur-PK");
                    string CurrentDateTime = Convert.ToString(dateTime, culture).Replace(':', '-').Replace('/', '-');

                    // Get an excel file name from user
                    saveFileDialog.FileName = Global.GetResourceString("VirtualRack_ReportName") + CurrentDateTime;
                    saveFileDialog.Title = Global.GetResourceString("ExcelReport_FileDialog_Title");
                    saveFileDialog.InitialDirectory = Global.AppPath();
                    saveFileDialog.Filter = Global.GetResourceString("Excel_FileDialog_Filter");
                    DialogResult saveReport = saveFileDialog.ShowDialog();
                    if (saveReport != DialogResult.OK)
                    {
                        return;
                    }


                    // Create Excel application object
                    ExcelReportFile = new Excel.Application();

                    // Add workbook to an excel object 
                    WorkBook = (Excel.Workbook)ExcelReportFile.Workbooks.Add("workbook");

                    // Get all the worksheets
                    WorkSheets = WorkBook.Sheets;

                    // Name the Colo Details worksheet 
                    WorkSheetColo = (Excel.Worksheet)WorkSheets.get_Item(1);
                    WorkSheetColo.Name = "Colo Details";

                    // Name the Rack Details worksheet
                    WorkSheetRack = (Excel.Worksheet)WorkSheets.get_Item(2);
                    WorkSheetRack.Name = "Rack Details";

                    // Name the Server Details worksheet
                    WorkSheetServer = (Excel.Worksheet)WorkSheets.get_Item(3);
                    WorkSheetServer.Name = "Server Details";

                    // Column counters
                    int ColoColCtr = 1;
                    int RackColCtr = 1;
                    int ServerColCtr = 1;

                    // Add table headers going cell by cell.
                    foreach (DataGridViewColumn dgvColm in dataGridViewColo.Columns)
                    {
                        WorkSheetColo.Cells[1, ColoColCtr] = dgvColm.HeaderText;
                        ColoColCtr++;
                    }

                    // Add table headers going cell by cell.
                    foreach (DataGridViewColumn dgvColm in dataGridViewRack.Columns)
                    {
                        WorkSheetRack.Cells[1, RackColCtr] = dgvColm.HeaderText;
                        RackColCtr++;
                    }

                    // Add table headers going cell by cell.
                    foreach (DataGridViewColumn dgvColm in dataGridViewServer.Columns)
                    {
                        WorkSheetServer.Cells[1, ServerColCtr] = dgvColm.HeaderText;
                        ServerColCtr++;
                    }

                    // Format header columns 
                    WorkSheetColo.get_Range("A1:B1", Missing.Value).Font.Bold = true;
                    WorkSheetColo.get_Range("A1", "B1").ColumnWidth = 15;
                    WorkSheetColo.get_Range("A1", "B1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    if (dgvrcColo != null)
                    {
                        WorkSheetColo.get_Range("1:" + (dgvrcColo.Count + 1) + "", Missing.Value).WrapText = true;
                        WorkSheetColo.get_Range("2:" + (dgvrcColo.Count + 1) + "", Missing.Value).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                    }

                    // Format header columns 
                    WorkSheetRack.get_Range("A1:D1", Missing.Value).Font.Bold = true;
                    WorkSheetRack.get_Range("A1", "D1").ColumnWidth = 15;
                    WorkSheetRack.get_Range("A1", "D1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    if (dgvrcRack != null)
                    {
                        WorkSheetRack.get_Range("1:" + (dgvrcRack.Count + 1) + "", Missing.Value).WrapText = true;
                        WorkSheetRack.get_Range("2:" + (dgvrcRack.Count + 1) + "", Missing.Value).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                    }

                    // Format header columns 
                    WorkSheetServer.get_Range("A1:E1", Missing.Value).Font.Bold = true;
                    WorkSheetServer.get_Range("A1", "E1").ColumnWidth = 15;
                    WorkSheetServer.get_Range("A1", "E1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    if (dgvrcServer != null)
                    {
                        WorkSheetServer.get_Range("1:" + (dgvrcServer.Count + 1) + "", Missing.Value).WrapText = true;
                        WorkSheetServer.get_Range("2:" + (dgvrcServer.Count + 1) + "", Missing.Value).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                    }
                    if (dgvrcColo != null)
                    {
                        // Get data into excel through datagrid going cell by cell
                        for (int rowIndex = 0; rowIndex < dgvrcColo.Count; rowIndex++)
                        {
                            DataGridViewRow dataRow = dgvrcColo[rowIndex];
                            for (int colIndex = 0; colIndex < dataGridViewColo.Columns.Count; colIndex++)
                            {
                                WorkSheetColo.Cells[rowIndex + 2, colIndex + 1] = (string)dataRow.Cells[colIndex].Value;
                            }
                        }
                    }
                    if (dgvrcRack != null)
                    {
                        // Get data into excel through datagrid going cell by cell
                        for (int rowIndex = 0; rowIndex < dgvrcRack.Count; rowIndex++)
                        {
                            DataGridViewRow dataRow = dgvrcRack[rowIndex];
                            for (int colIndex = 0; colIndex < dataGridViewRack.Columns.Count; colIndex++)
                            {
                                WorkSheetRack.Cells[rowIndex + 2, colIndex + 1] = (string)dataRow.Cells[colIndex].Value;
                            }
                        }
                    }

                    if (dgvrcServer != null)
                    {
                        // Get data into excel through datagrid going cell by cell
                        for (int rowIndex = 0; rowIndex < dgvrcServer.Count; rowIndex++)
                        {
                            DataGridViewRow dataRow = dgvrcServer[rowIndex];
                            for (int colIndex = 0; colIndex < dataGridViewServer.Columns.Count; colIndex++)
                            {
                                WorkSheetServer.Cells[rowIndex + 2, colIndex + 1] = (string)dataRow.Cells[colIndex].Value;
                            }
                        }
                    }

                    // Save report as excel  
                    switch (saveFileDialog.FilterIndex)
                    {
                        case 1:
                            // As .xslx
                            WorkBook.SaveAs(saveFileDialog.FileName,
                            Excel.XlFileFormat.xlOpenXMLWorkbook, Missing.Value, Missing.Value,
                            false, false, Excel.XlSaveAsAccessMode.xlNoChange,
                            Missing.Value, true, Missing.Value, Missing.Value, true);
                            break;

                        case 2:
                            // As .xsl
                            WorkBook.SaveAs(saveFileDialog.FileName,
                            Excel.XlFileFormat.xlTemplate, Missing.Value, Missing.Value,
                            false, false, Excel.XlSaveAsAccessMode.xlNoChange,
                            Missing.Value, true, Missing.Value, Missing.Value, true);
                            break;
                    }

                    MessageBox.Show(Global.GetResourceString("ExcelSaveData_Text") + saveFileDialog.FileName + ".",
                            Global.GetResourceString("MessageBoxCaption"),
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information,
                            MessageBoxDefaultButton.Button1
                            );
                }
                catch (System.Threading.ThreadAbortException)
                {
                    // Ignore thread abort exception
                }
                catch (RackException ex)
                {
                    // Exception from sub function. Pass on to entry point
                    throw new RackException("Export report to excel", ex.InnerException);
                }
                catch (Exception ex)
                {
                    // Exception from current function
                    throw new RackException("Export report to excel", ex);
                }
                finally
                {
                    // Clean up
                    if (ExcelReportFile != null)
                    {
                        // Close excel instances
                        if (WorkBook != null)
                        {
                            WorkBook.Close(false, "", false);
                        }

                        ExcelReportFile.Workbooks.Close();
                        ExcelReportFile.Quit();

                        // Release excel objects
                        Marshal.ReleaseComObject(WorkSheetColo);
                        Marshal.ReleaseComObject(WorkSheetRack);
                        Marshal.ReleaseComObject(WorkSheetServer);
                        Marshal.ReleaseComObject(WorkSheets);
                        Marshal.ReleaseComObject(WorkBook);
                        Marshal.ReleaseComObject(ExcelReportFile);

                        // Call Garbage Collector
                        GC.Collect();
                        GC.WaitForPendingFinalizers();

                        WorkBook = null;
                        ExcelReportFile = null;
                    }
                }
            }
            else
                return;
        }
        #endregion
    }
}
